package com.ezjie.ielts.core.helper;


import com.ezjie.ielts.application.YsApplication;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class AppDataBaseHelper extends SQLiteOpenHelper {

	// 数据库名称，
	private static final String DB_NAME = "ielts.db";
	/**
	 * 数据库版本，升级时修改
	 */
	private static final int DB_VERSION = 1;
	public static final String USERINFO = "user_info";// 用户信息
	public static final String SUBJECTS = "subjects_info"; // 科目表
	public static final String READ_CLASSIFY = "read_classify_info";// 阅读题型分类
	public static final String EXAMINFO = "exam_list_info"; // 真题列表
	public static final String FULLTEXT = "fulltext_info";// 全文
	public static final String GROUPINFO = "group_info";// 问题组
	public static final String QUESTIONINFO = "question_info";// 问题
	public static final String AUDIOFILE = "audio_file";

	public static final String USER_IDX = "user_index";// 用户索引
	public static final String SUB_IDX = "subjects_index";// 科目索引
	public static final String CLASSIFY_IDX = "classify_index";// 阅读题型分类索引
	public static final String EXAM_IDX = "exam_index";// 真题列表索引
	public static final String TEXT_IDX = "fulltext_index";// 全文索引
	public static final String GROUP_IDX = "group_index";// 问题组索引
	public static final String QUESTION_IDX = "quetion_index";// 问题索引
	public static final String AUDIO_IDX = "audio_index";// 音频文件索引

	private AppDataBaseHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	private static AppDataBaseHelper dbOpenHelper = null;

	/**
	 * 得到数据库实例
	 * 
	 * @param context
	 * @return 数据库的SQLiteOpenHelper对象
	 */
	public static synchronized AppDataBaseHelper getInstance(Context context) {
		if (dbOpenHelper == null) {
			dbOpenHelper = new AppDataBaseHelper(YsApplication.myApplication);
			return dbOpenHelper;
		} else {
			return dbOpenHelper;
		}
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		System.out.println("创建了！！！！");
		/**
		 * 创建用户表
		 */
		db.execSQL("CREATE TABLE IF NOT EXISTS " + USERINFO + " (" + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "uid" + " VARCHAR(30)," + "nick_name" + " VARCHAR(30)," + "mobile" + " VARCHAR(50)," + "email"
				+ " VARCHAR(50)," + "sex" + " VARCHAR(10)," + "head_url" + " VARCHAR(50)," + "login_key"
				+ " VARCHAR(50)," + "islogin" + " INTEGER," + "temp1" + " VARCHAR(50)," + "temp2" + " VARCHAR(50)"
				+ ");");
		db.execSQL("CREATE UNIQUE INDEX " + USER_IDX + " ON " + USERINFO + " (uid);");// 用户索引

		/**
		 * 创建科目表
		 */
		db.execSQL("CREATE TABLE IF NOT EXISTS " + SUBJECTS + " (" + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "classify_id" + " VARCHAR(20)," + "class_name" + " VARCHAR(30)," + "class_name_en" + " VARCHAR(30),"
				+ "type" + " INTEGER," + "class_type" + " VARCHAR(30)," + "num" + " INTEGER," + "temp1"
				+ " VARCHAR(50)," + "temp2" + " VARCHAR(50)" + ");");
		db.execSQL("CREATE UNIQUE INDEX " + SUB_IDX + " ON " + SUBJECTS + " (classify_id);");// 科目索引

		/**
		 * 创建阅读题型分类表
		 */
		db.execSQL("CREATE TABLE IF NOT EXISTS " + READ_CLASSIFY + " (" + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "cn" + " VARCHAR(50)," + "en" + " VARCHAR(50) COLLATE NOCASE," + "total" + " VARCHAR(20),"
				+ "position" + " VARCHAR(20)," + "answer_num" + " VARCHAR(20)," + "right_num" + " VARCHAR(20),"
				+ "temp1" + " VARCHAR(50)," + "temp2" + " VARCHAR(50)" + ");");
		db.execSQL("CREATE UNIQUE INDEX " + CLASSIFY_IDX + " ON " + READ_CLASSIFY + " (en);");// 阅读题型索引

		/**
		 * 创建真题列表
		 */
		db.execSQL("CREATE TABLE IF NOT EXISTS " + EXAMINFO + " (" + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "book" + " VARCHAR(10)," + "task" + " VARCHAR(10)," + "category_id" + " VARCHAR(10)," + "title"
				+ " VARCHAR(50)," + "title_en" + " VARCHAR(50)," + "android_img" + " VARCHAR(50)," + "ios_img"
				+ " VARCHAR(50)," + "type" + " INTEGER," + "classify_id" + " VARCHAR(10)," + "last_scored"
				+ " VARCHAR(50)," + "class_type" + " VARCHAR(10)," + "temp1" + " VARCHAR(50)," + "temp2"
				+ " VARCHAR(50)" + ");");
		db.execSQL("CREATE UNIQUE INDEX " + EXAM_IDX + " ON " + EXAMINFO + " (category_id);");// 真题列表索引

		/**
		 * 创建全文
		 */
		db.execSQL("CREATE TABLE IF NOT EXISTS " + FULLTEXT + " (" + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "category_id" + " VARCHAR(10)," + "content" + " TEXT," + "title" + " VARCHAR(50)," + "title_en"
				+ " VARCHAR(50)," + "android_img" + " VARCHAR(50)," + "ios_img" + " VARCHAR(50)," + "con_img"
				+ " VARCHAR(50)," + "temp1" + " VARCHAR(50)," + "temp2" + " VARCHAR(50)" + ");");
		db.execSQL("CREATE UNIQUE INDEX " + TEXT_IDX + " ON " + FULLTEXT + " (category_id);");// 全文索引

		/**
		 * 创建问题组
		 */
		db.execSQL("CREATE TABLE IF NOT EXISTS " + GROUPINFO + " (" + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "g_id" + " VARCHAR(10)," + "category_id" + " VARCHAR(10)," + "group_name" + " VARCHAR(50),"
				+ "group_cate" + " VARCHAR(50) COLLATE NOCASE," + "group_type" + " VARCHAR(10)," + "content" + " TEXT,"
				+ "img_1" + " TEXT," + "img_2" + " TEXT," + "audio" + " TEXT," + "audiofile" + " TEXT," + "is_right"
				+ " VARCHAR(10)," + "updateTime" + " VARCHAR(50)," + "book_name" + " VARCHAR(50)," + "task"
				+ " VARCHAR(50)," + "temp1" + " VARCHAR(50)," + "temp2" + " VARCHAR(50)" + ");");
		db.execSQL("CREATE UNIQUE INDEX " + GROUP_IDX + " ON " + GROUPINFO + " (g_id);");// 问题组索引

		/**
		 * 创建问题
		 */
		db.execSQL("CREATE TABLE IF NOT EXISTS " + QUESTIONINFO + " (" + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "g_id" + " VARCHAR(10)," + "question_id" + " VARCHAR(10)," + "question" + " TEXT," + "answer"
				+ " TEXT," + "describe" + " TEXT," + "is_right" + " VARCHAR(10)," + "no" + " VARCHAR(10)," + "temp1"
				+ " VARCHAR(50)," + "temp2" + " VARCHAR(50)" + ");");
		db.execSQL("CREATE UNIQUE INDEX " + QUESTION_IDX + " ON " + QUESTIONINFO + " (question_id);");// 问题索引

		/**
		 * 创建音频文件
		 */
		db.execSQL("CREATE TABLE IF NOT EXISTS " + AUDIOFILE + " (" + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "g_id" + " VARCHAR(10)," + "category_id" + " VARCHAR(10)," + "audiofile" + " TEXT" + ");");
		db.execSQL("CREATE UNIQUE INDEX " + AUDIO_IDX + " ON " + AUDIOFILE + " (g_id);");// 问题索引
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

		// upgradeTables(db, USERINFO);
	}

	/**
	 * Upgrade tables. In this method, the sequence is: <b>
	 * <p>
	 * [1] Rename the specified table as a temporary table.
	 * <p>
	 * [2] Create a new table which name is the specified name.
	 * <p>
	 * [3] Insert data into the new created table, data from the temporary
	 * table.
	 * <p>
	 * [4] Drop the temporary table. </b>
	 * 
	 * @param db
	 *            The database.
	 * @param tableName
	 *            The table name.
	 * @param columns
	 *            The columns range, format is "ColA, ColB, ColC, ... ColN";
	 */
	protected void upgradeTables(SQLiteDatabase db, String tableName) {
		try {
			db.beginTransaction();

			// 1, Rename table.
			String tempTableName = tableName + "_temp";
			String sql = "ALTER TABLE " + tableName + " RENAME TO " + tempTableName;
			db.execSQL(sql, null);

			// 2, Create table.
			onCreate(db);
			String[] columnNames = getColumnNames(db, tableName);

			// 3, Load data
			for (int i = 0; i < columnNames.length; i++) {
				sql = "INSERT INTO " + tableName + " (" + columnNames[i] + ") " + " SELECT " + columnNames[i]
						+ " FROM " + tempTableName;
			}

			db.execSQL(sql, null);

			// 4, Drop the temporary table.
			db.execSQL("DROP TABLE IF EXISTS " + tempTableName, null);

			db.setTransactionSuccessful();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
		}
	}

	protected String[] getColumnNames(SQLiteDatabase db, String tableName) {
		String[] columnNames = null;
		Cursor c = null;

		try {
			c = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
			if (null != c) {
				int columnIndex = c.getColumnIndex("name");
				if (-1 == columnIndex) {
					return null;
				}

				int index = 0;
				columnNames = new String[c.getCount()];
				for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
					columnNames[index] = c.getString(columnIndex);
					index++;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			c.close();
		}

		return columnNames;
	}

}
